--登陆sys执行
--创建表空间
CREATE TABLESPACE ruoyi DATAFILE
  'F:\tablespace\ruoyi01.DBF' SIZE 512M AUTOEXTEND ON NEXT 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建用户
drop user anan cascade;

CREATE USER anan
    IDENTIFIED BY mfkdgefhn123
    DEFAULT TABLESPACE ruoyi
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CONNECT TO anan;
GRANT RESOURCE TO anan;
ALTER USER anan DEFAULT ROLE ALL;

GRANT create synonym TO anan;
GRANT CREATE ANY TABLE TO anan;
GRANT CREATE ANY SEQUENCE TO anan;
GRANT CREATE ANY View TO anan;
GRANT UNLIMITED TABLESPACE TO anan;
GRANT CONNECT TO anan;
GRANT RESOURCE TO anan;
GRANT DBA TO anan;
GRANT CREATE DATABASE LINK TO anan;
grant debug connect session to anan;


--登陆anan
--建表
create table BI_DATASET
(
    dset_id   VARCHAR2(100) not null,
    dsid      VARCHAR2(100),
    dsname    VARCHAR2(100),
    use_type  VARCHAR2(100),
    name      VARCHAR2(100),
    cfg       VARCHAR2(100),
    crtdate   DATE,
    pri_table VARCHAR2(100)
)
    tablespace ruoyi
    pctfree 10
  initrans 1
  maxtrans 255;
comment on table BI_DATASET
  is '数据集';
alter table BI_DATASET
    add constraint PK_BI_DATASET primary key (DSET_ID)
        using index
    tablespace ruoyi
    pctfree 10
    initrans 2
    maxtrans 255;

-- Create table
create table BI_DATA_SOURCES
(
  ds_id     VARCHAR2(100) not null,
  link_type VARCHAR2(100),
  link_url  VARCHAR2(100),
  link_name VARCHAR2(100),
  link_pwd  VARCHAR2(100),
  dsname    VARCHAR2(100),
  jndi_name VARCHAR2(100),
  use       VARCHAR2(100),
  db_name   VARCHAR2(100)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table BI_DATA_SOURCES
  is 'BI数据源';
-- Create/Recreate primary, unique and foreign key constraints
alter table BI_DATA_SOURCES
  add constraint PK_BI_DATA_SOURCES primary key (DS_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- Create table
create table CLIENT
(
  id                 NUMBER(20) not null,
  del_flag           NUMBER(11) default 0,
  client_secret      VARCHAR2(255),
  home_uri           VARCHAR2(255),
  name               VARCHAR2(255),
  redirect_uri       VARCHAR2(255),
  create_by          VARCHAR2(64),
  create_time        DATE,
  update_by          VARCHAR2(64),
  update_time        DATE,
  remark             VARCHAR2(500),
  client_id          VARCHAR2(255),
  logo_uri           VARCHAR2(500),
  auto_authorization CHAR(1) default 'N'
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table CLIENT
  is '开放平台';
-- Add comments to the columns
comment on column CLIENT.id
  is 'ID';
comment on column CLIENT.del_flag
  is '删除标记 0 未删除 1 已删除';
comment on column CLIENT.client_secret
  is 'CLIENT_SECRET';
comment on column CLIENT.home_uri
  is '网站主页';
comment on column CLIENT.name
  is '网站名称';
comment on column CLIENT.redirect_uri
  is '回调地址';
comment on column CLIENT.create_by
  is '创建人';
comment on column CLIENT.create_time
  is '创建时间';
comment on column CLIENT.update_by
  is '修改人';
comment on column CLIENT.update_time
  is '修改时间';
comment on column CLIENT.remark
  is '备注';
comment on column CLIENT.client_id
  is 'CLIENT_ID';
comment on column CLIENT.logo_uri
  is '网站LOGO';
comment on column CLIENT.auto_authorization
  is '自动授权 Y 自动 N 手动';
-- Create/Recreate primary, unique and foreign key constraints
alter table CLIENT
  add constraint PK_OAUTH2_CLIENT primary key (ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- Create table
create table DATA_SOURCES
(
  data_source_id NUMBER(10) not null,
  name           VARCHAR2(100),
  url            VARCHAR2(100),
  username       VARCHAR2(100),
  password       VARCHAR2(100),
  driver         VARCHAR2(100),
  code           VARCHAR2(100),
  databasetype   VARCHAR2(100),
  remark         VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table DATA_SOURCES
  is '数据源';
-- Add comments to the columns
comment on column DATA_SOURCES.name
  is '数据源名称';
comment on column DATA_SOURCES.url
  is 'URL';
comment on column DATA_SOURCES.username
  is '用户名';
comment on column DATA_SOURCES.password
  is '密码';
comment on column DATA_SOURCES.driver
  is '驱动';
comment on column DATA_SOURCES.code
  is '代码';
comment on column DATA_SOURCES.databasetype
  is '数据库类型';
comment on column DATA_SOURCES.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table DATA_SOURCES
  add constraint PK_DATA_SOURCES primary key (DATA_SOURCE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 18、代码生成业务表
-- ----------------------------
-- Create table
create table GEN_TABLE
(
  table_id        NUMBER(20) not null,
  table_name      VARCHAR2(200) default '',
  table_comment   VARCHAR2(500) default '',
  class_name      VARCHAR2(100) default '',
  tpl_category    VARCHAR2(200) default 'crud',
  package_name    VARCHAR2(100),
  module_name     VARCHAR2(30),
  business_name   VARCHAR2(30),
  function_name   VARCHAR2(50),
  function_author VARCHAR2(50),
  gen_type        CHAR(1) default '0',
  gen_path        VARCHAR2(200) default '/',
  options         VARCHAR2(1000),
  create_by       VARCHAR2(64) default '',
  create_time     DATE,
  update_by       VARCHAR2(64) default '',
  update_time     DATE,
  remark          VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table GEN_TABLE
  is '代码生成业务表';
-- Add comments to the columns
comment on column GEN_TABLE.table_id
  is '编号';
comment on column GEN_TABLE.table_name
  is '表名称';
comment on column GEN_TABLE.table_comment
  is '表描述';
comment on column GEN_TABLE.class_name
  is '实体类名称';
comment on column GEN_TABLE.tpl_category
  is '使用的模板（crud单表操作 tree树表操作）';
comment on column GEN_TABLE.package_name
  is '生成包路径';
comment on column GEN_TABLE.module_name
  is '生成模块名';
comment on column GEN_TABLE.business_name
  is '生成业务名';
comment on column GEN_TABLE.function_name
  is '生成功能名';
comment on column GEN_TABLE.function_author
  is '生成功能作者';
comment on column GEN_TABLE.gen_type
  is '生成代码方式（0zip压缩包 1自定义路径）';
comment on column GEN_TABLE.gen_path
  is '生成路径（不填默认项目路径）';
comment on column GEN_TABLE.options
  is '其它生成选项';
comment on column GEN_TABLE.create_by
  is '创建者';
comment on column GEN_TABLE.create_time
  is '创建时间';
comment on column GEN_TABLE.update_by
  is '更新者';
comment on column GEN_TABLE.update_time
  is '更新时间';
comment on column GEN_TABLE.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table GEN_TABLE
  add constraint PK_GEN_TABLE primary key (TABLE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 19、代码生成业务表字段
-- ----------------------------
-- Create table
create table GEN_TABLE_COLUMN
(
  column_id      NUMBER(20) not null,
  table_id       VARCHAR2(64),
  column_name    VARCHAR2(200),
  column_comment VARCHAR2(500),
  column_type    VARCHAR2(100),
  java_type      VARCHAR2(500),
  java_field     VARCHAR2(200),
  is_pk          CHAR(1),
  is_increment   CHAR(1),
  is_required    CHAR(1),
  is_insert      CHAR(1),
  is_edit        CHAR(1),
  is_list        CHAR(1),
  is_query       CHAR(1),
  query_type     VARCHAR2(200) default 'EQ',
  html_type      VARCHAR2(200),
  dict_type      VARCHAR2(200) default '',
  sort           NUMBER(4),
  create_by      VARCHAR2(64) default '',
  create_time    DATE,
  update_by      VARCHAR2(64) default '',
  update_time    DATE
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table GEN_TABLE_COLUMN
  is '代码生成业务表字段';
-- Add comments to the columns
comment on column GEN_TABLE_COLUMN.column_id
  is '编号';
comment on column GEN_TABLE_COLUMN.table_id
  is '归属表编号';
comment on column GEN_TABLE_COLUMN.column_name
  is '列名称';
comment on column GEN_TABLE_COLUMN.column_comment
  is '列描述';
comment on column GEN_TABLE_COLUMN.column_type
  is '列类型';
comment on column GEN_TABLE_COLUMN.java_type
  is 'JAVA类型';
comment on column GEN_TABLE_COLUMN.java_field
  is 'JAVA字段名';
comment on column GEN_TABLE_COLUMN.is_pk
  is '是否主键（1是）';
comment on column GEN_TABLE_COLUMN.is_increment
  is '是否自增（1是）';
comment on column GEN_TABLE_COLUMN.is_required
  is '是否必填（1是）';
comment on column GEN_TABLE_COLUMN.is_insert
  is '是否为插入字段（1是）';
comment on column GEN_TABLE_COLUMN.is_edit
  is '是否编辑字段（1是）';
comment on column GEN_TABLE_COLUMN.is_list
  is '是否列表字段（1是）';
comment on column GEN_TABLE_COLUMN.is_query
  is '是否查询字段（1是）';
comment on column GEN_TABLE_COLUMN.query_type
  is '查询方式（等于、不等于、大于、小于、范围）';
comment on column GEN_TABLE_COLUMN.html_type
  is '显示类型（文本框、文本域、下拉框、复选框、单选框、日期控件）';
comment on column GEN_TABLE_COLUMN.dict_type
  is '字典类型';
comment on column GEN_TABLE_COLUMN.sort
  is '排序';
comment on column GEN_TABLE_COLUMN.create_by
  is '创建者';
comment on column GEN_TABLE_COLUMN.create_time
  is '创建时间';
comment on column GEN_TABLE_COLUMN.update_by
  is '更新者';
comment on column GEN_TABLE_COLUMN.update_time
  is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table GEN_TABLE_COLUMN
  add constraint PK_GEN_TABLE_COLUMN primary key (COLUMN_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- ----------------------------
-- 1、存储每一个已配置的 jobDetail 的详细信息
-- ----------------------------
-- Create table
create table QRTZ_JOB_DETAILS
(
  sched_name        VARCHAR2(120) not null,
  job_name          VARCHAR2(200) not null,
  job_group         VARCHAR2(200) not null,
  description       VARCHAR2(250),
  job_class_name    VARCHAR2(250) not null,
  is_durable        VARCHAR2(1) not null,
  is_nonconcurrent  VARCHAR2(1) not null,
  is_update_data    VARCHAR2(1) not null,
  requests_recovery VARCHAR2(1) not null,
  job_data          BLOB
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index IDX_QRTZ_J_GRP on QRTZ_JOB_DETAILS (SCHED_NAME, JOB_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_J_REQ_RECOVERY on QRTZ_JOB_DETAILS (SCHED_NAME, REQUESTS_RECOVERY)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_JOB_DETAILS
  add constraint QRTZ_JOB_DETAILS_PK primary key (SCHED_NAME, JOB_NAME, JOB_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- ----------------------------
-- 2、 存储已配置的 Trigger 的信息
-- ----------------------------
-- Create table
create table QRTZ_TRIGGERS
(
  sched_name     VARCHAR2(120) not null,
  trigger_name   VARCHAR2(200) not null,
  trigger_group  VARCHAR2(200) not null,
  job_name       VARCHAR2(200) not null,
  job_group      VARCHAR2(200) not null,
  description    VARCHAR2(250),
  next_fire_time NUMBER(13),
  prev_fire_time NUMBER(13),
  priority       NUMBER(13),
  trigger_state  VARCHAR2(16) not null,
  trigger_type   VARCHAR2(8) not null,
  start_time     NUMBER(13) not null,
  end_time       NUMBER(13),
  calendar_name  VARCHAR2(200),
  misfire_instr  NUMBER(2),
  job_data       BLOB
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index IDX_QRTZ_T_C on QRTZ_TRIGGERS (SCHED_NAME, CALENDAR_NAME)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_G on QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_J on QRTZ_TRIGGERS (SCHED_NAME, JOB_NAME, JOB_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_JG on QRTZ_TRIGGERS (SCHED_NAME, JOB_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_NEXT_FIRE_TIME on QRTZ_TRIGGERS (SCHED_NAME, NEXT_FIRE_TIME)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_NFT_MISFIRE on QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_NFT_ST on QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_STATE, NEXT_FIRE_TIME)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_NFT_ST_MISFIRE on QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_STATE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_NFT_ST_MISFIRE_GRP on QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_GROUP, TRIGGER_STATE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_N_G_STATE on QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_GROUP, TRIGGER_STATE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_N_STATE on QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_STATE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_QRTZ_T_STATE on QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_STATE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_TRIGGERS
  add constraint QRTZ_TRIGGERS_PK primary key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table QRTZ_TRIGGERS
  add constraint QRTZ_TRIGGER_TO_JOBS_FK foreign key (SCHED_NAME, JOB_NAME, JOB_GROUP)
  references QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP);


-- ----------------------------
-- 5、 Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型，JobStore 并不知道如何存储实例的时候)
-- ----------------------------
-- Create table
create table QRTZ_BLOB_TRIGGERS
(
  sched_name    VARCHAR2(120) not null,
  trigger_name  VARCHAR2(200) not null,
  trigger_group VARCHAR2(200) not null,
  blob_data     BLOB
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_BLOB_TRIGGERS
  add constraint QRTZ_BLOB_TRIG_PK primary key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;

alter table QRTZ_BLOB_TRIGGERS
  add constraint QRTZ_BLOB_TRIG_TO_TRIG_FK foreign key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  references QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);


-- ----------------------------
-- 6、 以 Blob 类型存储存放日历信息， quartz可配置一个日历来指定一个时间范围
-- ----------------------------
-- Create table
create table QRTZ_CALENDARS
(
  sched_name    VARCHAR2(120) not null,
  calendar_name VARCHAR2(200) not null,
  calendar      BLOB not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_CALENDARS
  add constraint QRTZ_CALENDARS_PK primary key (SCHED_NAME, CALENDAR_NAME)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;


-- ----------------------------
-- 4、 存储 Cron Trigger，包括 Cron 表达式和时区信息
-- ----------------------------
-- Create table
create table QRTZ_CRON_TRIGGERS
(
  sched_name      VARCHAR2(120) not null,
  trigger_name    VARCHAR2(200) not null,
  trigger_group   VARCHAR2(200) not null,
  cron_expression VARCHAR2(120) not null,
  time_zone_id    VARCHAR2(80)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_CRON_TRIGGERS
  add constraint QRTZ_CRON_TRIG_PK primary key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table QRTZ_CRON_TRIGGERS
  add constraint QRTZ_CRON_TRIG_TO_TRIG_FK foreign key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  references QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);


-- ----------------------------
-- 8、 存储与已触发的 Trigger 相关的状态信息，以及相联 Job 的执行信息
-- ----------------------------
-- Create table
create table QRTZ_FIRED_TRIGGERS
(
  sched_name        VARCHAR2(120) not null,
  entry_id          VARCHAR2(95) not null,
  trigger_name      VARCHAR2(200) not null,
  trigger_group     VARCHAR2(200) not null,
  instance_name     VARCHAR2(200) not null,
  fired_time        NUMBER(13) not null,
  sched_time        NUMBER(13) not null,
  priority          NUMBER(13) not null,
  state             VARCHAR2(16) not null,
  job_name          VARCHAR2(200),
  job_group         VARCHAR2(200),
  is_nonconcurrent  VARCHAR2(1),
  requests_recovery VARCHAR2(1)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate indexes
create index IDX_QRTZ_FT_INST_JOB_REQ_RCVRY on QRTZ_FIRED_TRIGGERS (SCHED_NAME, INSTANCE_NAME, REQUESTS_RECOVERY)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
create index IDX_QRTZ_FT_JG on QRTZ_FIRED_TRIGGERS (SCHED_NAME, JOB_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
create index IDX_QRTZ_FT_J_G on QRTZ_FIRED_TRIGGERS (SCHED_NAME, JOB_NAME, JOB_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
create index IDX_QRTZ_FT_TG on QRTZ_FIRED_TRIGGERS (SCHED_NAME, TRIGGER_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
create index IDX_QRTZ_FT_TRIG_INST_NAME on QRTZ_FIRED_TRIGGERS (SCHED_NAME, INSTANCE_NAME)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
create index IDX_QRTZ_FT_T_G on QRTZ_FIRED_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_FIRED_TRIGGERS
  add constraint QRTZ_FIRED_TRIGGER_PK primary key (SCHED_NAME, ENTRY_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;


-- ----------------------------
-- 10、 存储程序的悲观锁的信息(假如使用了悲观锁)
-- ----------------------------
-- Create table
create table QRTZ_LOCKS
(
  sched_name VARCHAR2(120) not null,
  lock_name  VARCHAR2(40) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_LOCKS
  add constraint QRTZ_LOCKS_PK primary key (SCHED_NAME, LOCK_NAME)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- ----------------------------
-- 7、 存储已暂停的 Trigger 组的信息
-- ----------------------------
-- Create table
create table QRTZ_PAUSED_TRIGGER_GRPS
(
  sched_name    VARCHAR2(120) not null,
  trigger_group VARCHAR2(200) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_PAUSED_TRIGGER_GRPS
  add constraint QRTZ_PAUSED_TRIG_GRPS_PK primary key (SCHED_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;


-- ----------------------------
-- 9、 存储少量的有关 Scheduler 的状态信息，假如是用于集群中，可以看到其他的 Scheduler 实例
-- ----------------------------
-- Create table
create table QRTZ_SCHEDULER_STATE
(
  sched_name        VARCHAR2(120) not null,
  instance_name     VARCHAR2(200) not null,
  last_checkin_time NUMBER(13) not null,
  checkin_interval  NUMBER(13) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_SCHEDULER_STATE
  add constraint QRTZ_SCHEDULER_STATE_PK primary key (SCHED_NAME, INSTANCE_NAME)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- ----------------------------
-- 3、 存储简单的 Trigger，包括重复次数，间隔，以及已触发的次数
-- ----------------------------
-- Create table
create table QRTZ_SIMPLE_TRIGGERS
(
  sched_name      VARCHAR2(120) not null,
  trigger_name    VARCHAR2(200) not null,
  trigger_group   VARCHAR2(200) not null,
  repeat_count    NUMBER(7) not null,
  repeat_interval NUMBER(12) not null,
  times_triggered NUMBER(10) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_SIMPLE_TRIGGERS
  add constraint QRTZ_SIMPLE_TRIG_PK primary key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
alter table QRTZ_SIMPLE_TRIGGERS
  add constraint QRTZ_SIMPLE_TRIG_TO_TRIG_FK foreign key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  references QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);

-- Create table
create table QRTZ_SIMPROP_TRIGGERS
(
  sched_name    VARCHAR2(120) not null,
  trigger_name  VARCHAR2(200) not null,
  trigger_group VARCHAR2(200) not null,
  str_prop_1    VARCHAR2(512),
  str_prop_2    VARCHAR2(512),
  str_prop_3    VARCHAR2(512),
  int_prop_1    NUMBER(10),
  int_prop_2    NUMBER(10),
  long_prop_1   NUMBER(13),
  long_prop_2   NUMBER(13),
  dec_prop_1    NUMBER(13,4),
  dec_prop_2    NUMBER(13,4),
  bool_prop_1   VARCHAR2(1),
  bool_prop_2   VARCHAR2(1)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table QRTZ_SIMPROP_TRIGGERS
  add constraint QRTZ_SIMPROP_TRIG_PK primary key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255;
alter table QRTZ_SIMPROP_TRIGGERS
  add constraint QRTZ_SIMPROP_TRIG_TO_TRIG_FK foreign key (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
  references QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);

-- ----------------------------
-- 13、参数配置表
-- ----------------------------
-- Create table
create table SYS_CONFIG
(
  config_id    NUMBER(20) not null,
  config_name  VARCHAR2(100) default '',
  config_key   VARCHAR2(100) default '',
  config_value VARCHAR2(100) default '',
  config_type  CHAR(1) default 'N',
  create_by    VARCHAR2(64) default '',
  create_time  DATE,
  update_by    VARCHAR2(64) default '',
  update_time  DATE,
  remark       VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_CONFIG
  is '参数配置表';
-- Add comments to the columns
comment on column SYS_CONFIG.config_id
  is '参数主键seq_sys_config.nextval';
comment on column SYS_CONFIG.config_name
  is '参数名称';
comment on column SYS_CONFIG.config_key
  is '参数键名';
comment on column SYS_CONFIG.config_value
  is '参数键值';
comment on column SYS_CONFIG.config_type
  is '系统内置（Y是 N否）';
comment on column SYS_CONFIG.create_by
  is '创建者';
comment on column SYS_CONFIG.create_time
  is '创建时间';
comment on column SYS_CONFIG.update_by
  is '更新者';
comment on column SYS_CONFIG.update_time
  is '更新时间';
comment on column SYS_CONFIG.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_CONFIG
  add constraint PK_SYS_CONFIG primary key (CONFIG_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table SYS_CUSTOMER
(
  id              NUMBER(10) not null,
  code            VARCHAR2(80),
  name            VARCHAR2(255) not null,
  c_customerup_id NUMBER(10),
  c_cusrank_id    NUMBER(10) not null,
  areamng_id      NUMBER(10),
  c_city_id       NUMBER(10),
  contacter       VARCHAR2(255),
  mobile          VARCHAR2(20),
  address         VARCHAR2(255),
  remark          VARCHAR2(255),
  is_o2o          CHAR(1),
  creationdate    DATE,
  modifieddate    DATE,
  isactive        CHAR(1) not null,
  is_online       CHAR(1),
  c_cusattrib9_id NUMBER(10)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_CUSTOMER
  is '经销商档案';
-- Add comments to the columns
comment on column SYS_CUSTOMER.code
  is '编号';
comment on column SYS_CUSTOMER.name
  is '经销商';
comment on column SYS_CUSTOMER.c_customerup_id
  is '上级经销商';
comment on column SYS_CUSTOMER.c_cusrank_id
  is '经销商级别';
comment on column SYS_CUSTOMER.areamng_id
  is '区域经理';
comment on column SYS_CUSTOMER.c_city_id
  is '城市';
comment on column SYS_CUSTOMER.contacter
  is '联系人';
comment on column SYS_CUSTOMER.mobile
  is '手机';
comment on column SYS_CUSTOMER.address
  is '地址';
comment on column SYS_CUSTOMER.remark
  is '备注';
comment on column SYS_CUSTOMER.is_o2o
  is '是否参与O2O';
comment on column SYS_CUSTOMER.creationdate
  is '创建时间';
comment on column SYS_CUSTOMER.modifieddate
  is '修改时间';
comment on column SYS_CUSTOMER.isactive
  is '可用';
comment on column SYS_CUSTOMER.is_online
  is '是否上线';
comment on column SYS_CUSTOMER.c_cusattrib9_id
  is '经销商性质';


-- Create table
create table SYS_DATA_SEARCH
(
  data_source_id NUMBER(10),
  search_name    VARCHAR2(100),
  search_name_en VARCHAR2(100),
  search_tree_id NUMBER(10),
  data_search_id NUMBER(10) not null,
  sql            CLOB,
  admin_sql      CLOB,
  create_by      VARCHAR2(64),
  create_time    DATE,
  update_by      VARCHAR2(64),
  update_time    DATE,
  remark         VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_DATA_SEARCH
  is '查询管理';
-- Add comments to the columns
comment on column SYS_DATA_SEARCH.data_source_id
  is '数据源ID';
comment on column SYS_DATA_SEARCH.search_name
  is '名称';
comment on column SYS_DATA_SEARCH.search_name_en
  is '名称(拼音)';
comment on column SYS_DATA_SEARCH.search_tree_id
  is '查询树ID';
comment on column SYS_DATA_SEARCH.data_search_id
  is 'ID';
comment on column SYS_DATA_SEARCH.sql
  is 'sql';
comment on column SYS_DATA_SEARCH.admin_sql
  is 'admin_sql';
comment on column SYS_DATA_SEARCH.create_by
  is '创建者';
comment on column SYS_DATA_SEARCH.create_time
  is '创建时间';
comment on column SYS_DATA_SEARCH.update_by
  is '更新者';
comment on column SYS_DATA_SEARCH.update_time
  is '更新时间';
comment on column SYS_DATA_SEARCH.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DATA_SEARCH
  add constraint PK_SYS_DATA_SEARCH primary key (DATA_SEARCH_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SYS_DATA_SEARCH
  add constraint SYS_DATA_SEARCH01 unique (SEARCH_NAME_EN)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 1、部门表
-- ----------------------------
-- Create table
create table SYS_DEPT
(
  dept_id     NUMBER(20) not null,
  parent_id   NUMBER(20) default 0,
  ancestors   VARCHAR2(50) default '',
  dept_name   VARCHAR2(30) default '',
  order_num   NUMBER(4) default 0,
  leader      VARCHAR2(20),
  phone       VARCHAR2(11),
  email       VARCHAR2(50),
  status      CHAR(1) default '0',
  del_flag    CHAR(1) default '0',
  create_by   VARCHAR2(64) default '',
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  dd_dept_id  NUMBER(10)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_DEPT
  is '部门信息表';
-- Add comments to the columns
comment on column SYS_DEPT.dept_id
  is '部门主键seq_sys_dept.nextval';
comment on column SYS_DEPT.parent_id
  is '父部门id';
comment on column SYS_DEPT.ancestors
  is '祖级列表';
comment on column SYS_DEPT.dept_name
  is '部门名称';
comment on column SYS_DEPT.order_num
  is '显示顺序';
comment on column SYS_DEPT.leader
  is '负责人';
comment on column SYS_DEPT.phone
  is '联系电话';
comment on column SYS_DEPT.email
  is '邮箱';
comment on column SYS_DEPT.status
  is '部门状态（0正常 1停用）';
comment on column SYS_DEPT.del_flag
  is '删除标志（0代表存在 2代表删除）';
comment on column SYS_DEPT.create_by
  is '创建者';
comment on column SYS_DEPT.create_time
  is '创建时间';
comment on column SYS_DEPT.update_by
  is '更新者';
comment on column SYS_DEPT.update_time
  is '更新时间';
comment on column SYS_DEPT.dd_dept_id
  is '钉钉部门ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DEPT
  add constraint PK_SYS_DEPT primary key (DEPT_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 12、字典数据表
-- ----------------------------
-- Create table
create table SYS_DICT_DATA
(
  dict_code   NUMBER(20) not null,
  dict_sort   NUMBER(4) default 0,
  dict_label  VARCHAR2(100) default '',
  dict_value  VARCHAR2(100) default '',
  dict_type   VARCHAR2(100) default '',
  css_class   VARCHAR2(100),
  list_class  VARCHAR2(100),
  is_default  CHAR(1) default 'N',
  status      CHAR(1) default '0',
  create_by   VARCHAR2(64) default '',
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  remark      VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_DICT_DATA
  is '字典数据表';
-- Add comments to the columns
comment on column SYS_DICT_DATA.dict_code
  is '字典主键seq_sys_dict_data.nextval';
comment on column SYS_DICT_DATA.dict_sort
  is '字典排序';
comment on column SYS_DICT_DATA.dict_label
  is '字典标签';
comment on column SYS_DICT_DATA.dict_value
  is '字典键值';
comment on column SYS_DICT_DATA.dict_type
  is '字典类型';
comment on column SYS_DICT_DATA.css_class
  is '样式属性（其他样式扩展）';
comment on column SYS_DICT_DATA.list_class
  is '表格回显样式';
comment on column SYS_DICT_DATA.is_default
  is '是否默认（Y是 N否）';
comment on column SYS_DICT_DATA.status
  is '状态（0正常 1停用）';
comment on column SYS_DICT_DATA.create_by
  is '创建者';
comment on column SYS_DICT_DATA.create_time
  is '创建时间';
comment on column SYS_DICT_DATA.update_by
  is '更新者';
comment on column SYS_DICT_DATA.update_time
  is '更新时间';
comment on column SYS_DICT_DATA.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DICT_DATA
  add constraint PK_SYS_DICT_DATA primary key (DICT_CODE)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 11、字典类型表
-- ----------------------------
-- Create table
create table SYS_DICT_TYPE
(
  dict_id     NUMBER(20) not null,
  dict_name   VARCHAR2(100) default '',
  dict_type   VARCHAR2(100) default '',
  status      CHAR(1) default '0',
  create_by   VARCHAR2(64) default '',
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  remark      VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_DICT_TYPE
  is '字典类型表';
-- Add comments to the columns
comment on column SYS_DICT_TYPE.dict_id
  is '字典主键seq_sys_dict_type.nextval';
comment on column SYS_DICT_TYPE.dict_name
  is '字典名称';
comment on column SYS_DICT_TYPE.dict_type
  is '字典类型';
comment on column SYS_DICT_TYPE.status
  is '状态（0正常 1停用）';
comment on column SYS_DICT_TYPE.create_by
  is '创建者';
comment on column SYS_DICT_TYPE.create_time
  is '创建时间';
comment on column SYS_DICT_TYPE.update_by
  is '更新者';
comment on column SYS_DICT_TYPE.update_time
  is '更新时间';
comment on column SYS_DICT_TYPE.remark
  is '备注';
-- Create/Recreate indexes
create unique index SYS_DICT_TYPE_INDEX1 on SYS_DICT_TYPE (DICT_TYPE)
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DICT_TYPE
  add constraint PK_SYS_DICT_TYPE primary key (DICT_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 15、定时任务调度表
-- ----------------------------
-- Create table
create table SYS_JOB
(
  job_id          NUMBER(20) not null,
  job_name        VARCHAR2(64) default '' not null,
  job_group       VARCHAR2(64) default '' not null,
  invoke_target   VARCHAR2(500) not null,
  cron_expression VARCHAR2(255) default '',
  misfire_policy  VARCHAR2(20) default '3',
  concurrent      CHAR(1) default '1',
  status          CHAR(1) default '0',
  create_by       VARCHAR2(64) default '',
  create_time     DATE,
  update_by       VARCHAR2(64) default '',
  update_time     DATE,
  remark          VARCHAR2(500) default ''
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_JOB
  is '定时任务调度表';
-- Add comments to the columns
comment on column SYS_JOB.job_id
  is '任务主键seq_sys_job.nextval';
comment on column SYS_JOB.job_name
  is '任务名称';
comment on column SYS_JOB.job_group
  is '任务组名';
comment on column SYS_JOB.invoke_target
  is '调用目标字符串';
comment on column SYS_JOB.cron_expression
  is 'cron执行表达式';
comment on column SYS_JOB.misfire_policy
  is '计划执行错误策略（1立即执行 2执行一次 3放弃执行）';
comment on column SYS_JOB.concurrent
  is '是否并发执行（0允许 1禁止）';
comment on column SYS_JOB.status
  is '状态（0正常 1暂停）';
comment on column SYS_JOB.create_by
  is '创建者';
comment on column SYS_JOB.create_time
  is '创建时间';
comment on column SYS_JOB.update_by
  is '更新者';
comment on column SYS_JOB.update_time
  is '更新时间';
comment on column SYS_JOB.remark
  is '备注信息';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_JOB
  add constraint PK_SYS_JOB primary key (JOB_ID, JOB_NAME, JOB_GROUP)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 16、定时任务调度日志表
-- ----------------------------
-- Create table
create table SYS_JOB_LOG
(
  job_log_id     NUMBER(20) not null,
  job_name       VARCHAR2(64) not null,
  job_group      VARCHAR2(64) not null,
  invoke_target  VARCHAR2(500) not null,
  job_message    VARCHAR2(500),
  status         CHAR(1) default '0',
  exception_info VARCHAR2(2000) default '',
  create_time    DATE
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_JOB_LOG
  is '定时任务调度日志表';
-- Add comments to the columns
comment on column SYS_JOB_LOG.job_log_id
  is '日志主键seq_sys_job_log.nextval';
comment on column SYS_JOB_LOG.job_name
  is '任务名称';
comment on column SYS_JOB_LOG.job_group
  is '任务组名';
comment on column SYS_JOB_LOG.invoke_target
  is '调用目标字符串';
comment on column SYS_JOB_LOG.job_message
  is '日志信息';
comment on column SYS_JOB_LOG.status
  is '执行状态（0正常 1失败）';
comment on column SYS_JOB_LOG.exception_info
  is '异常信息';
comment on column SYS_JOB_LOG.create_time
  is '创建时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_JOB_LOG
  add constraint PK_SYS_JOB_LOG primary key (JOB_LOG_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 14、系统访问记录
-- ----------------------------
-- Create table
create table SYS_LOGININFOR
(
  info_id        NUMBER(20) not null,
  user_name      VARCHAR2(50) default '',
  ipaddr         VARCHAR2(50) default '',
  login_location VARCHAR2(255) default '',
  browser        VARCHAR2(50) default '',
  os             VARCHAR2(50) default '',
  status         CHAR(1) default '0',
  msg            VARCHAR2(255) default '',
  login_time     DATE
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_LOGININFOR
  is '系统访问记录';
-- Add comments to the columns
comment on column SYS_LOGININFOR.info_id
  is '访问主键seq_seq_sys_logininfor.nextval';
comment on column SYS_LOGININFOR.user_name
  is '登录账号';
comment on column SYS_LOGININFOR.ipaddr
  is '登录IP地址';
comment on column SYS_LOGININFOR.login_location
  is '登录地点';
comment on column SYS_LOGININFOR.browser
  is '浏览器类型';
comment on column SYS_LOGININFOR.os
  is '操作系统';
comment on column SYS_LOGININFOR.status
  is '登录状态（0成功 1失败）';
comment on column SYS_LOGININFOR.msg
  is '提示消息';
comment on column SYS_LOGININFOR.login_time
  is '访问时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_LOGININFOR
  add constraint PK_SYS_LOGININFOR primary key (INFO_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 5、菜单权限表
-- ----------------------------
-- Create table
create table SYS_MENU
(
  menu_id     NUMBER(20) not null,
  menu_name   VARCHAR2(50) not null,
  parent_id   NUMBER(20) default 0,
  order_num   NUMBER(4) default 0,
  path        VARCHAR2(200) default '',
  component   VARCHAR2(255),
  is_frame    NUMBER(1) default 1,
  is_cache    NUMBER(1) default 0,
  menu_type   CHAR(1) default '',
  visible     CHAR(1) default 0,
  status      CHAR(1) default 0,
  perms       VARCHAR2(100),
  icon        VARCHAR2(100) default '#',
  create_by   VARCHAR2(64) default '',
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  remark      VARCHAR2(500) default ''
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_MENU
  is '菜单权限表';
-- Add comments to the columns
comment on column SYS_MENU.menu_id
  is '菜单主键seq_sys_post.nextval';
comment on column SYS_MENU.menu_name
  is '菜单名称';
comment on column SYS_MENU.parent_id
  is '父菜单ID';
comment on column SYS_MENU.order_num
  is '显示顺序';
comment on column SYS_MENU.path
  is '请求地址';
comment on column SYS_MENU.component
  is '路由地址';
comment on column SYS_MENU.is_frame
  is '是否为外链（0是 1否）';
comment on column SYS_MENU.is_cache
  is '是否缓存（0缓存 1不缓存）';
comment on column SYS_MENU.menu_type
  is '菜单类型（M目录 C菜单 F按钮）';
comment on column SYS_MENU.visible
  is '菜单状态（0显示 1隐藏）';
comment on column SYS_MENU.status
  is '菜单状态（0正常 1停用）';
comment on column SYS_MENU.perms
  is '权限标识';
comment on column SYS_MENU.icon
  is '菜单图标';
comment on column SYS_MENU.create_by
  is '创建者';
comment on column SYS_MENU.create_time
  is '创建时间';
comment on column SYS_MENU.update_by
  is '更新者';
comment on column SYS_MENU.update_time
  is '更新时间';
comment on column SYS_MENU.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_MENU
  add constraint PK_SYS_MENU primary key (MENU_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 17、通知公告表
-- ----------------------------
-- Create table
create table SYS_NOTICE
(
  notice_id      NUMBER(20) not null,
  notice_title   VARCHAR2(50) not null,
  notice_type    CHAR(1) not null,
  notice_content CLOB,
  status         CHAR(1) default '0',
  create_by      VARCHAR2(64) default '',
  create_time    DATE,
  update_by      VARCHAR2(64) default '',
  update_time    DATE,
  remark         VARCHAR2(255)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_NOTICE
  is '通知公告表';
-- Add comments to the columns
comment on column SYS_NOTICE.notice_id
  is '公告主键seq_sys_notice.nextval';
comment on column SYS_NOTICE.notice_title
  is '公告标题';
comment on column SYS_NOTICE.notice_type
  is '公告类型（1通知 2公告）';
comment on column SYS_NOTICE.notice_content
  is '公告内容';
comment on column SYS_NOTICE.status
  is '公告状态（0正常 1关闭）';
comment on column SYS_NOTICE.create_by
  is '创建者';
comment on column SYS_NOTICE.create_time
  is '创建时间';
comment on column SYS_NOTICE.update_by
  is '更新者';
comment on column SYS_NOTICE.update_time
  is '更新时间';
comment on column SYS_NOTICE.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_NOTICE
  add constraint PK_SYS_NOTICE primary key (NOTICE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 10、操作日志记录
-- ----------------------------
-- Create table
create table SYS_OPER_LOG
(
  oper_id        NUMBER(20) not null,
  title          VARCHAR2(50) default '',
  business_type  NUMBER(2) default 0,
  method         VARCHAR2(100) default '',
  request_method VARCHAR2(10) default '',
  operator_type  NUMBER(1) default 0,
  oper_name      VARCHAR2(50) default '',
  dept_name      VARCHAR2(50) default '',
  oper_url       VARCHAR2(255) default '',
  oper_ip        VARCHAR2(50) default '',
  oper_location  VARCHAR2(255) default '',
  oper_param     VARCHAR2(4000) default '',
  json_result    VARCHAR2(2000) default '',
  status         NUMBER(1) default 0,
  error_msg      VARCHAR2(2000) default '',
  oper_time      DATE
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_OPER_LOG
  is '操作日志记录';
-- Add comments to the columns
comment on column SYS_OPER_LOG.oper_id
  is '日志主键seq_sys_oper_log.nextval';
comment on column SYS_OPER_LOG.title
  is '模块标题';
comment on column SYS_OPER_LOG.business_type
  is '业务类型（0其它 1新增 2修改 3删除）';
comment on column SYS_OPER_LOG.method
  is '方法名称';
comment on column SYS_OPER_LOG.request_method
  is '请求方式';
comment on column SYS_OPER_LOG.operator_type
  is '操作类别（0其它 1后台用户 2手机端用户）';
comment on column SYS_OPER_LOG.oper_name
  is '操作人员';
comment on column SYS_OPER_LOG.dept_name
  is '部门名称';
comment on column SYS_OPER_LOG.oper_url
  is '请求URL';
comment on column SYS_OPER_LOG.oper_ip
  is '主机地址';
comment on column SYS_OPER_LOG.oper_location
  is '操作地点';
comment on column SYS_OPER_LOG.oper_param
  is '请求参数';
comment on column SYS_OPER_LOG.json_result
  is '返回参数';
comment on column SYS_OPER_LOG.status
  is '操作状态（0正常 1异常）';
comment on column SYS_OPER_LOG.error_msg
  is '错误消息';
comment on column SYS_OPER_LOG.oper_time
  is '操作时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_OPER_LOG
  add constraint PK_SYS_OPER_LOG primary key (OPER_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 3、岗位信息表
-- ----------------------------
-- Create table
create table SYS_POST
(
  post_id     NUMBER(20) not null,
  post_code   VARCHAR2(64) not null,
  post_name   VARCHAR2(50) not null,
  post_sort   NUMBER(4) not null,
  status      CHAR(1) not null,
  create_by   VARCHAR2(64) default '',
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  remark      VARCHAR2(500)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_POST
  is '岗位信息表';
-- Add comments to the columns
comment on column SYS_POST.post_id
  is '岗位主键seq_sys_post.nextval';
comment on column SYS_POST.post_code
  is '岗位编码';
comment on column SYS_POST.post_name
  is '岗位名称';
comment on column SYS_POST.post_sort
  is '显示顺序';
comment on column SYS_POST.status
  is '状态（0正常 1停用）';
comment on column SYS_POST.create_by
  is '创建者';
comment on column SYS_POST.create_time
  is '创建时间';
comment on column SYS_POST.update_by
  is '更新者';
comment on column SYS_POST.update_time
  is '更新时间';
comment on column SYS_POST.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_POST
  add constraint PK_SYS_POST primary key (POST_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 4、角色信息表
-- ----------------------------
-- Create table
create table SYS_ROLE
(
  role_id                 NUMBER(20) not null,
  role_name               VARCHAR2(30) not null,
  role_key                VARCHAR2(100) not null,
  role_sort               NUMBER(4) not null,
  data_scope              CHAR(1) default '1',
  menu_check_strictly     NUMBER(1) default 1,
  dept_check_strictly     NUMBER(1) default 1,
  status                  CHAR(1) not null,
  del_flag                CHAR(1) default '0',
  create_by               VARCHAR2(64) default '',
  create_time             DATE,
  update_by               VARCHAR2(64) default '',
  update_time             DATE,
  remark                  VARCHAR2(500),
  is_default              CHAR(1) default 'N',
  customer_check_strictly NUMBER(1) default 1,
  customer_data_scope     CHAR(1) default '1',
  including_stores        NUMBER(1) default 1
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_ROLE
  is '角色信息表';
-- Add comments to the columns
comment on column SYS_ROLE.role_id
  is '角色主键seq_sys_post.nextval';
comment on column SYS_ROLE.role_name
  is '角色名称';
comment on column SYS_ROLE.role_key
  is '角色权限字符串';
comment on column SYS_ROLE.role_sort
  is '显示顺序';
comment on column SYS_ROLE.data_scope
  is '数据范围（1：全部数据权限 2：自定数据权限 3:本部门数据权限 4:本部门及以及数据权限 5:公本人数据权限）';
comment on column SYS_ROLE.menu_check_strictly
  is '菜单树选择项是否关联显示';
comment on column SYS_ROLE.dept_check_strictly
  is '部门树选择项是否关联显示';
comment on column SYS_ROLE.status
  is '角色状态（0正常 1停用）';
comment on column SYS_ROLE.del_flag
  is '删除标志（0代表存在 2代表删除）';
comment on column SYS_ROLE.create_by
  is '创建者';
comment on column SYS_ROLE.create_time
  is '创建时间';
comment on column SYS_ROLE.update_by
  is '更新者';
comment on column SYS_ROLE.update_time
  is '更新时间';
comment on column SYS_ROLE.remark
  is '备注';
comment on column SYS_ROLE.is_default
  is '默认角色';
comment on column SYS_ROLE.customer_check_strictly
  is '经销商树选择项是否关联显示';
comment on column SYS_ROLE.customer_data_scope
  is '经销商数据范围（1：全部数据权限 2：自定数据权限 3:本部门数据权限 4:本部门及以及数据权限 5:公本人数据权限）';
comment on column SYS_ROLE.including_stores
  is '包含店仓（0不包含 1包含）';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_ROLE
  add constraint PK_SYS_ROLE primary key (ROLE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table SYS_ROLE_CUSTOMER
(
  role_id         NUMBER(20) not null,
  c_customer_id   NUMBER(20) not null,
  permission_type CHAR(1) default 0
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_ROLE_CUSTOMER
  is '角色和经销商关联表';
-- Add comments to the columns
comment on column SYS_ROLE_CUSTOMER.role_id
  is '角色ID';
comment on column SYS_ROLE_CUSTOMER.c_customer_id
  is '经销商ID';
comment on column SYS_ROLE_CUSTOMER.permission_type
  is '权限类型(0:无权限 1:全部 2:所属全部直营门店 3:部分门店)';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_ROLE_CUSTOMER
  add constraint PK_SYS_ROLE_CUSTOMER primary key (ROLE_ID, C_CUSTOMER_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 8、角色和部门关联表  角色1-N部门
-- ----------------------------
-- Create table
create table SYS_ROLE_DEPT
(
  role_id NUMBER(20) not null,
  dept_id NUMBER(20) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_ROLE_DEPT
  is '角色和部门关联表';
-- Add comments to the columns
comment on column SYS_ROLE_DEPT.role_id
  is '角色ID';
comment on column SYS_ROLE_DEPT.dept_id
  is '部门ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_ROLE_DEPT
  add constraint PK_SYS_ROLE_DEPT primary key (ROLE_ID, DEPT_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 7、角色和菜单关联表  角色1-N菜单
-- ----------------------------
-- Create table
create table SYS_ROLE_MENU
(
  role_id NUMBER(20) not null,
  menu_id NUMBER(20) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_ROLE_MENU
  is '角色和菜单关联表';
-- Add comments to the columns
comment on column SYS_ROLE_MENU.role_id
  is '角色ID';
comment on column SYS_ROLE_MENU.menu_id
  is '菜单ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_ROLE_MENU
  add constraint PK_SYS_ROLE_MENU primary key (ROLE_ID, MENU_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table SYS_ROLE_STORE
(
  role_id       NUMBER(20),
  c_store_id    NUMBER(20),
  c_customer_id NUMBER(20)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_ROLE_STORE
  is '角色和店仓关联表';
-- Add comments to the columns
comment on column SYS_ROLE_STORE.role_id
  is '角色ID';
comment on column SYS_ROLE_STORE.c_store_id
  is '仓店ID';
comment on column SYS_ROLE_STORE.c_customer_id
  is '经销商ID';

-- Create table
create table SYS_SEARCH_TREE
(
  search_tree_id   NUMBER(10) not null,
  parent_id        NUMBER(10),
  search_tree_name VARCHAR2(100),
  isactive         CHAR(1),
  description      VARCHAR2(100),
  order_num        NUMBER(4),
  ancestors        VARCHAR2(50)
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_SEARCH_TREE
  is '查询树';
-- Add comments to the columns
comment on column SYS_SEARCH_TREE.search_tree_id
  is '查询树ID';
comment on column SYS_SEARCH_TREE.parent_id
  is '父级树ID';
comment on column SYS_SEARCH_TREE.search_tree_name
  is '名称';
comment on column SYS_SEARCH_TREE.isactive
  is '可用';
comment on column SYS_SEARCH_TREE.description
  is '备注';
comment on column SYS_SEARCH_TREE.order_num
  is '排序';
comment on column SYS_SEARCH_TREE.ancestors
  is '祖级列表';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_SEARCH_TREE
  add constraint PK_SEARCH_TREE primary key (SEARCH_TREE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


-- Create table
create table SYS_STORE
(
  id                NUMBER(10) not null,
  code              VARCHAR2(180),
  name              VARCHAR2(255) not null,
  c_sotype_id       NUMBER(10),
  c_storekind_id    NUMBER(10) not null,
  c_customer_id     NUMBER(10) not null,
  c_customerup_id   NUMBER(10),
  c_province_id     NUMBER(10),
  c_city_id         NUMBER(10),
  c_district_id     NUMBER(10),
  c_pricearea_id    NUMBER(10) not null,
  c_viptype_id      NUMBER(10),
  contactor         VARCHAR2(40),
  mobil             VARCHAR2(255),
  address           VARCHAR2(255),
  c_integralarea_id NUMBER(10),
  rentbegin         NUMBER(8),
  storetype         NUMBER(10),
  c_storeattrib1_id NUMBER(10),
  c_storeattrib3_id NUMBER(10),
  is_allo2ostorage  CHAR(1),
  c_mall_id         NUMBER(10),
  lbs               VARCHAR2(100),
  creationdate      DATE,
  modifieddate      DATE,
  isactive          CHAR(1) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_STORE
  is '店仓档案';
-- Add comments to the columns
comment on column SYS_STORE.code
  is '店仓编号';
comment on column SYS_STORE.name
  is '店仓名称';
comment on column SYS_STORE.c_sotype_id
  is '来源';
comment on column SYS_STORE.c_storekind_id
  is '店仓种类';
comment on column SYS_STORE.c_customer_id
  is '所属经销商';
comment on column SYS_STORE.c_customerup_id
  is '上级经销商';
comment on column SYS_STORE.c_province_id
  is '省份';
comment on column SYS_STORE.c_city_id
  is '城市';
comment on column SYS_STORE.c_district_id
  is '区县';
comment on column SYS_STORE.c_pricearea_id
  is '价格区域';
comment on column SYS_STORE.c_viptype_id
  is 'VIP类型';
comment on column SYS_STORE.contactor
  is '联系人';
comment on column SYS_STORE.mobil
  is '手机';
comment on column SYS_STORE.address
  is '地址';
comment on column SYS_STORE.c_integralarea_id
  is 'VIP积分区域';
comment on column SYS_STORE.rentbegin
  is '开店日期';
comment on column SYS_STORE.storetype
  is '店仓类型';
comment on column SYS_STORE.c_storeattrib1_id
  is '店仓业态';
comment on column SYS_STORE.c_storeattrib3_id
  is '店铺性质';
comment on column SYS_STORE.is_allo2ostorage
  is '全款参与O2O';
comment on column SYS_STORE.c_mall_id
  is '商城';
comment on column SYS_STORE.lbs
  is '经纬度(高德地图) ';
comment on column SYS_STORE.creationdate
  is '创建时间';
comment on column SYS_STORE.modifieddate
  is '修改时间';
comment on column SYS_STORE.isactive
  is '可用';

-- ----------------------------
-- 2、用户信息表
-- ----------------------------
-- Create table
create table SYS_USER
(
  user_id     NUMBER(20) not null,
  dept_id     NUMBER(20),
  user_name   VARCHAR2(30) not null,
  nick_name   VARCHAR2(30) default '',
  user_type   VARCHAR2(2) default '00',
  email       VARCHAR2(50) default '',
  phonenumber VARCHAR2(11) default '',
  sex         CHAR(1) default '0',
  avatar      VARCHAR2(100) default '',
  password    VARCHAR2(100) default '',
  status      CHAR(1) default '0',
  del_flag    CHAR(1) default '0',
  login_ip    VARCHAR2(50) default '',
  login_date  DATE,
  create_by   VARCHAR2(64),
  create_time DATE,
  update_by   VARCHAR2(64) default '',
  update_time DATE,
  remark      VARCHAR2(500) default '',
  login_type  VARCHAR2(30) default ''
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_USER
  is '用户信息表';
-- Add comments to the columns
comment on column SYS_USER.user_id
  is '用户主键seq_sys_user.nextval';
comment on column SYS_USER.dept_id
  is '部门ID';
comment on column SYS_USER.user_name
  is '用户账号';
comment on column SYS_USER.nick_name
  is '用户昵称';
comment on column SYS_USER.user_type
  is '用户类型（00系统用户 01注册用户）';
comment on column SYS_USER.email
  is '用户邮箱';
comment on column SYS_USER.phonenumber
  is '手机号码';
comment on column SYS_USER.sex
  is '用户性别（0男 1女 2未知）';
comment on column SYS_USER.avatar
  is '头像路径';
comment on column SYS_USER.password
  is '密码';
comment on column SYS_USER.status
  is '帐号状态（0正常 1停用）';
comment on column SYS_USER.del_flag
  is '删除标志（0代表存在 2代表删除）';
comment on column SYS_USER.login_ip
  is '最后登录IP';
comment on column SYS_USER.login_date
  is '最后登录时间';
comment on column SYS_USER.create_by
  is '创建者';
comment on column SYS_USER.create_time
  is '创建时间';
comment on column SYS_USER.update_by
  is '更新者';
comment on column SYS_USER.update_time
  is '更新时间';
comment on column SYS_USER.remark
  is '备注';
comment on column SYS_USER.login_type
  is '登陆方式';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_USER
  add constraint PK_SYS_USER primary key (USER_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 9、用户与岗位关联表  用户1-N岗位
-- ----------------------------
-- Create table
create table SYS_USER_POST
(
  user_id NUMBER(20) not null,
  post_id NUMBER(20) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_USER_POST
  is '用户与岗位关联表';
-- Add comments to the columns
comment on column SYS_USER_POST.user_id
  is '用户ID';
comment on column SYS_USER_POST.post_id
  is '岗位ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_USER_POST
  add constraint PK_SYS_USER_POST primary key (USER_ID, POST_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ----------------------------
-- 6、用户和角色关联表  用户N-1角色
-- ----------------------------
-- Create table
create table SYS_USER_ROLE
(
  user_id NUMBER(20) not null,
  role_id NUMBER(20) not null
)
tablespace ruoyi
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYS_USER_ROLE
  is '用户和角色关联表';
-- Add comments to the columns
comment on column SYS_USER_ROLE.user_id
  is '用户ID';
comment on column SYS_USER_ROLE.role_id
  is '角色ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_USER_ROLE
  add constraint PK_SYS_USER_ROLE primary key (USER_ID, ROLE_ID)
  using index
  tablespace ruoyi
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


--序列号
create sequence SEQ_BI_DATASET minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 20;
create sequence SEQ_BI_DATA_SOURCES minvalue 1 maxvalue 9999999999999999999999999999 start with 21 increment by 1 cache 20;
create sequence SEQ_CLIENT minvalue 1 maxvalue 9999999999999999999999999999 start with 30 increment by 1 cache 20;
create sequence SEQ_DATA_SOURCES minvalue 1 maxvalue 9999999999999999999999999999 start with 110 increment by 1 cache 20;
create sequence seq_gen_table
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_gen_table_column
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_config
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence SEQ_SYS_DATA_SEARCH minvalue 1 maxvalue 9999999999999999999999999999 start with 181 increment by 1 cache 20;
create sequence seq_sys_dept
 increment by 1
 start with 200
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_sys_dict_data
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_dict_type
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_sys_job
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_sys_job_log
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_logininfor
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_menu
 increment by 1
 start with 2000
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_sys_notice
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_oper_log
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
 create sequence seq_sys_post
 increment by 1
 start with 10
 nomaxvalue
 nominvalue
 cache 20;
create sequence seq_sys_role
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;
create sequence SEQ_SYS_SEARCH_TREE minvalue 1 maxvalue 9999999999999999999999999 start with 18 increment by 1 nocache;
create sequence seq_sys_user
 increment by 1
 start with 100
 nomaxvalue
 nominvalue
 cache 20;

--触发器
create or replace trigger tr_bi_dataset
  before  insert
  on bi_dataset
  for each row
declare
  -- local variables here
begin
  select SEQ_BI_DATASET.nextval into :new.dset_id from dual;
end tr_bi_dataset;

create or replace trigger tr_bi_data_sources
  before  insert
  on bi_data_sources
  for each row
declare
  -- local variables here
begin
  select SEQ_BI_DATA_SOURCES.nextval into :new.ds_id from dual;
end tr_bi_data_sources;





--type
create or replace type pop_store as object(
 c_store_id  NUMBER(10),
 store_name  VARCHAR2(50),
 c_customer_id NUMBER(10),
 c_customer_name varchar(100),
 cusrank_id  NUMBER(2),
 business_type   VARCHAR2(9),
 c_customerup_id NUMBER(10)
)

create or replace type pop_store_table as table of pop_store


create or replace view v_store as
select c.id,c.code,c.name,c.c_sotype_id,c.c_storekind_id,c.c_customer_id,c.c_customerup_id,c.c_province_id,c.c_city_id,
       c.c_district_id,c.c_pricearea_id,c.c_viptype_id,c.contactor,c.mobil,c.address,c.c_integralarea_id,c.rentbegin,
       c.storetype,c.c_storeattrib1_id,c.c_storeattrib3_id,c.is_allo2ostorage,c.c_mall_id,c.lbs,c.creationdate,
       c.modifieddate,c.isactive
  from c_store@portal80 c, c_customer@portal80 s
 where c.c_customer_id = s.id
   and c.isactive = 'Y'
   and s.isactive = 'Y'
   and c.name not like '%停%'
   and c.name not like '%测试%'
   and c.c_storekind_id = 1
   and s.c_cusrank_id = 2
   and (c.isclose = 'N' or c.creationdate >= sysdate - 15)
union
select c.id,c.code,c.name,c.c_sotype_id,c.c_storekind_id,c.c_customer_id,c.c_customerup_id,c.c_province_id,c.c_city_id,
       c.c_district_id,c.c_pricearea_id,c.c_viptype_id,c.contactor,c.mobil,c.address,c.c_integralarea_id,c.rentbegin,
       c.storetype,c.c_storeattrib1_id,c.c_storeattrib3_id,c.is_allo2ostorage,c.c_mall_id,c.lbs,c.creationdate,
       c.modifieddate,c.isactive
  from c_store@portal80 c, c_customer@portal80 s
 where c.c_customerup_id = s.id
   and c.isactive = 'Y'
   and s.isactive = 'Y'
   and s.is_online = 'Y'
   and c.name not like '%停%'
   and c.name not like '%测试%'
   and c.c_storekind_id = 1
   and s.c_cusrank_id = 2
   and (c.isclose = 'N' or c.creationdate >= sysdate - 15);


create or replace view v_customer as
select id,code,name,c_customerup_id,c_cusrank_id,areamng_id,c_city_id,contacter,mobile,
       address,remark,is_o2o,creationdate,modifieddate,isactive,is_online,c_cusattrib9_id
  from c_customer@portal80 a
 where a.isactive = 'Y'
   and a.c_cusrank_id in (1,2)
   and a.is_online = 'Y'
   and a.name <> '广州戈美其'
   and a.name <> '贵州总经销商'
   and a.name <> '济南总经销商'
   and a.name <> '老贵阳总经销商'
   and a.name <> '深圳总经销商'
   and a.name <> '温州总经销商'
   and a.name <> '直营公司'
   and a.name not like '%停%'
   and a.name not like '%测试%'
   AND a.name NOT LIKE '%旧%'
   AND a.name NOT LIKE '%老%'
union
select a.id,a.code,a.name,a.c_customerup_id,a.c_cusrank_id,a.areamng_id,a.c_city_id,a.contacter,a.mobile,
       a.address,a.remark,a.is_o2o,a.creationdate,a.modifieddate,a.isactive,a.is_online,a.c_cusattrib9_id
  from c_customer@portal80 a,c_customer@portal80 b
 where a.c_customerup_id=b.id
   and a.isactive = 'Y'
   and a.c_cusrank_id = 3
   and b.isactive='Y'
   and a.name not like '%停%'
   and a.name not like '%测试%'
   AND a.name NOT LIKE '%旧%'
   AND a.name NOT LIKE '%老%'
   and b.name <> '广州戈美其'
   and b.name <> '贵州总经销商'
   and b.name <> '济南总经销商'
   and b.name <> '老贵阳总经销商'
   and b.name <> '深圳总经销商'
   and b.name <> '温州总经销商'
   and b.name <> '直营公司'
   and b.c_cusrank_id=2;
